﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.IO;
using System.Data.OleDb;

namespace DbfViewer.Models
{
	public class OleDbTableRepository : ITableRepository
	{
		public DataTable Load(string fileName)
		{
			if (!File.Exists(fileName))
			{
				throw new FileNotFoundException("File " + fileName + " not found.");
			}
			var table = new DataTable(Path.GetFileNameWithoutExtension(fileName));
			string query = "SELECT * FROM \'" + Path.GetFileName(fileName) + "\';";
			using (var connection = new OleDbConnection(GetConnectionString(fileName)))
			{
				using (var command = new OleDbCommand(query, connection))
				{
					using (var adapter = new OleDbDataAdapter(command))
					{
						adapter.Fill(table);
					}
				}
			}
			return table;
		}

		private string GetConnectionString(string fileName)
		{
			var builder = new OleDbConnectionStringBuilder();
			builder.DataSource = fileName;
			builder.Provider = "VFPOLEDB.1";
			return builder.ConnectionString;
		}

		public void Save(string fileName, DataTable table)
		{
			string createQuery = SqlGenerator.GenerateCreateQuery(Path.GetFileName(fileName), table);
			string insertQuery = SqlGenerator.GenerateInsertQuery(Path.GetFileName(fileName), table);
			CreateFile(fileName);
			using (var connection = new OleDbConnection(GetConnectionString(fileName)))
			{
				connection.Open();
				CreateTable(createQuery, connection);
				InsertRecords(insertQuery, table, connection);
				connection.Close();
			}
		}

		private void CreateFile(string fileName)
		{
			if (!File.Exists(fileName))
			{
				FileInfo fi = new FileInfo(fileName);
				fi.Create();
			}
		}

		private void InsertRecords(string query, DataTable table, OleDbConnection connection)
		{
			foreach (DataRow row in table.Rows)
			{
				using (var command = new OleDbCommand(query, connection))
				{
					foreach (DataColumn column in table.Columns)
					{
						command.Parameters.AddWithValue(column.ColumnName.ToString(), row[column.ColumnName.ToString()]);
					}
					command.Transaction = connection.BeginTransaction();
					try
					{
						command.ExecuteNonQuery();
						command.Transaction.Commit();
					}
					catch (OleDbException ex)
					{
						command.Transaction.Rollback();
						throw ex;
					}

				}
			}
		}

		private void CreateTable(string query, OleDbConnection connection)
		{
			using (var command = new OleDbCommand(query, connection))
			{
				command.Transaction = connection.BeginTransaction();
				try
				{
					command.ExecuteNonQuery();
					command.Transaction.Commit();
				}
				catch (OleDbException ex)
				{
					command.Transaction.Rollback();
					throw ex;
				}
			}
		}
	}
}
